package com.dtmobile.spark.biz.kpi

import org.apache.spark.sql.{SaveMode, SparkSession}

/**
  * Created by shenkaili on 17-3-31.
  */
class KpibusinessDayAnaly(ANALY_DATE: String,SDB: String, DDB: String, warhouseDir: String) {
  val cal_date = ANALY_DATE.substring(0, 4) + "-" + ANALY_DATE.substring(4).substring(0,2) + "-" + ANALY_DATE.substring(6)+ " " +"00:00:00"
  val kpibusinessday=
    s"""
       |sum(browsedownloadvisits),
       |sum(videoservicevisits),
       |sum(instantmessagevisits),
       |sum(appvisits),
       |sum(browsedownloadbusiness),
       |sum(videoservicebusiness),
       |sum(instantmessagebusiness),
       |sum(appbusiness),
       |sum(dnsQuerySucc),
       |sum(dnsQueryAtt),
       |sum(tcpSetupSucc),
       |sum(tcpSetupReq),
       |sum(BearerULTCPRetransmit),
       |sum(BearerULTCPTransmit),
       |sum(BearerDLTCPRetransmit),
       |sum(BearerDLTCPTransmit),
       |sum(BearerULTCPMissequence),
       |sum(BearerDLTCPMissequence),
       |sum(pageresp),
       |sum(pagereq),
       |sum(pageresptimeall),
       |sum(pageshowsucc),
       |sum(pageshowtimeall),
       |sum(httpdownflow),
       |sum(httpdowntime),
       |sum(mediasucc),
       |sum(mediareq),
       |sum(mediadownflow),
       |sum(mediadowntime),
       |sum(ServiceIMSucc),
       |sum(ServiceIMReq),
       |sum(readvisits),
       |sum(wbvisits),
       |sum(navigationvisits),
       |sum(musicvisits),
       |sum(gamevisits),
       |sum(payvisits),
       |sum(Animevisits),
       |sum(mailvisits),
       |sum(p2pvisits),
       |sum(voipvisits),
       |sum(MultimediaMsgvisits),
       |sum(financialvisits),
       |sum(securityvisits),
       |sum(shoppingvisits),
       |sum(travelvisits),
       |sum(cloudstoragevisits),
       |sum(othervisits),
       |sum(readbusiness),
       |sum(wbbusiness),
       |sum(navigationbusiness),
       |sum(musicbusiness),
       |sum(gamebusiness),
       |sum(paybusiness),
       |sum(Animebusiness),
       |sum(mailbusiness),
       |sum(p2pbusiness),
       |sum(voipbusiness),
       |sum(MultimediaMsgbusiness),
       |sum(financialbusiness),
       |sum(securitybusiness),
       |sum(shoppingbusiness),
       |sum(travelbusiness),
       |sum(cloudstoragebusiness),
       |sum(otherbusiness),
       |sum(mediaRespTimeall),
       |sum(mediaResp),
       |sum(ServiceIMTrans),
       |sum(ServiceIMFlow),
       |sum(ServiceIMTime),
       |sum(readsucc),
       |sum(readreq),
       |sum(readresptimeall),
       |sum(readresp),
       |sum(readdownflow),
       |sum(readdowntime),
       |sum(wbsucc),
       |sum(wbreq),
       |sum(wbresptimeall),
       |sum(wbresp),
       |sum(wbdownflow),
       |sum(wbdowntime),
       |sum(navigationsucc),
       |sum(navigationreq),
       |sum(navigationresptimeall),
       |sum(navigationresp),
       |sum(navigationdownflow),
       |sum(navigationdowntime),
       |sum(musicsucc),
       |sum(musicreq),
       |sum(musicresptimeall),
       |sum(musicresp),
       |sum(musicdownflow),
       |sum(musicdowntime),
       |sum(appsucc),
       |sum(appreq),
       |sum(appresptimeall),
       |sum(appresp),
       |sum(appdownflow),
       |sum(appdowntime),
       |sum(gamesucc),
       |sum(gamereq),
       |sum(gameresptimeall),
       |sum(gameresp),
       |sum(gamedownflow),
       |sum(gamedowntime),
       |sum(paysucc),
       |sum(payreq),
       |sum(payresptimeall),
       |sum(payresp),
       |sum(paydownflow),
       |sum(paydowntime),
       |sum(Animesucc),
       |sum(Animereq),
       |sum(Animeresptimeall),
       |sum(Animeresp),
       |sum(Animedownflow),
       |sum(Animedowntime),
       |sum(mailsucc),
       |sum(mailreq),
       |sum(mailresptimeall),
       |sum(mailresp),
       |sum(maildownflow),
       |sum(maildowntime),
       |sum(P2Psucc),
       |sum(P2Preq),
       |sum(P2Presptimeall),
       |sum(P2Presp),
       |sum(P2Pdownflow),
       |sum(P2Pdowntime),
       |sum(VoIPsucc),
       |sum(VoIPreq),
       |sum(VoIPresptimeall),
       |sum(VoIPresp),
       |sum(VoIPdownflow),
       |sum(VoIPdowntime),
       |sum(MultimediaMsgsucc),
       |sum(MultimediaMsgreq),
       |sum(MultimediaMsgresptimeall),
       |sum(MultimediaMsgresp),
       |sum(MultimediaMsgdownflow),
       |sum(MultimediaMsgdowntime),
       |sum(financialsucc),
       |sum(financialreq),
       |sum(financialresptimeall),
       |sum(financialresp),
       |sum(financialdownflow),
       |sum(financialdowntime),
       |sum(securitysucc),
       |sum(securityreq),
       |sum(securityresptimeall),
       |sum(securityresp),
       |sum(securitydownflow),
       |sum(securitydowntime),
       |sum(shoppingsucc),
       |sum(shoppingreq),
       |sum(shoppingresptimeall),
       |sum(shoppingresp),
       |sum(shoppingdownflow),
       |sum(shoppingdowntime),
       |sum(travelsucc),
       |sum(travelreq),
       |sum(travelresptimeall),
       |sum(travelresp),
       |sum(traveldownflow),
       |sum(traveldowntime),
       |sum(cloudstoragesucc),
       |sum(cloudstoragereq),
       |sum(cloudstorageresptimeall),
       |sum(cloudstorageresp),
       |sum(cloudstoragedownflow),
       |sum(cloudstoragedowntime),
       |sum(internetsucc),
       |sum(internetreq),
       |sum(internetresptimeall),
       |sum(internetresp),
       |sum(internetdownflow),
       |sum(internetdowntime),
       |sum(migusucc),
       |sum(migureq),
       |sum(miguresptimeall),
       |sum(miguresp),
       |sum(migudownflow),
       |sum(migudowntime),
       |sum(othersucc),
       |sum(otherreq),
       |sum(otherresptimeall),
       |sum(otherresp),
       |sum(otherdownflow),
       |sum(otherdowntime),
       |sum(instantmessageTime),
       |sum(readTime),
       |sum(wbTime),
       |sum(navigationTime),
       |sum(videoserviceTime),
       |sum(musicTime),
       |sum(appTime),
       |sum(gameTime),
       |sum(payTime),
       |sum(AnimeTime),
       |sum(mailTime),
       |sum(p2pTime),
       |sum(voipTime),
       |sum(MultimediaMsgTime),
       |sum(browsedownloadTime),
       |sum(financialTime),
       |sum(securityTime),
       |sum(shoppingTime),
       |sum(travelTime),
       |sum(cloudstorageTime),
       |sum(internetTime),
       |sum(miguTime),
       |sum(otherTime),
       |sum(browsesurfresp),
       |sum(browsesurfreq),
       |sum(browsesurfresptimeall),
       |sum(browsesurfshowsucc),
       |sum(browsesurfshowtimeall),
       |sum(browsesurfdownflow),
       |sum(browsesurfdowntime),
       |sum(browsesurfFlow),
       |sum(browsesurfTime),
       |sum(browsetencentresp),
       |sum(browsetencentreq),
       |sum(browsetencentresptimeall),
       |sum(browsetencentshowsucc),
       |sum(browsetencentshowtimeall),
       |sum(browsetencentdownflow),
       |sum(browsetencentdowntime),
       |sum(browsetencentFlow),
       |sum(browsetencentTime),
       |sum(browseqqNewsresp),
       |sum(browseqqNewsreq),
       |sum(browseqqNewsresptimeall),
       |sum(browseqqNewsshowsucc),
       |sum(browseqqNewsshowtimeall),
       |sum(browseqqNewsdownflow),
       |sum(browseqqNewsdowntime),
       |sum(browseqqNewsFlow),
       |sum(browseqqNewsTime),
       |sum(browsetoutiaoresp),
       |sum(browsetoutiaoreq),
       |sum(browsetoutiaoresptimeall),
       |sum(browsetoutiaoshowsucc),
       |sum(browsetoutiaoshowtimeall),
       |sum(browsetoutiaodownflow),
       |sum(browsetoutiaodowntime),
       |sum(browsetoutiaoFlow),
       |sum(browsetoutiaoTime),
       |sum(browseChromeresp),
       |sum(browseChromereq),
       |sum(browseChromeresptimeall),
       |sum(browseChromeshowsucc),
       |sum(browseChromeshowtimeall),
       |sum(browseChromedownflow),
       |sum(browseChromedowntime),
       |sum(browseChromeFlow),
       |sum(browseChromeTime),
       |sum(otherhttpresp),
       |sum(otherhttpreq),
       |sum(otherhttpresptimeall),
       |sum(otherhttpshowsucc),
       |sum(otherhttpshowtimeall),
       |sum(otherhttpdownflow),
       |sum(otherhttpdowntime),
       |sum(otherhttpFlow),
       |sum(otherhttpTime),
       |sum(videomobileTVsucc),
       |sum(videomobileTVreq),
       |sum(videomobileTVresptimeall),
       |sum(videomobileTVresp),
       |sum(videomobileTVdownflow),
       |sum(videomobileTVdowntime),
       |sum(videomobileTVFlow),
       |sum(videomobileTVTime),
       |sum(videomiguvideosucc),
       |sum(videomiguvideoreq),
       |sum(videomiguvideoresptimeall),
       |sum(videomiguvideoresp),
       |sum(videomiguvideodownflow),
       |sum(videomiguvideodowntime),
       |sum(videomiguvideoFlow),
       |sum(videomiguvideoTime),
       |sum(videoiqiyisucc),
       |sum(videoiqiyireq),
       |sum(videoiqiyiresptimeall),
       |sum(videoiqiyiresp),
       |sum(videoiqiyidownflow),
       |sum(videoiqiyidowntime),
       |sum(videoiqiyiFlow),
       |sum(videoiqiyiTime),
       |sum(imFechatsucc),
       |sum(imFechatreq),
       |sum(imFechatresptimeall),
       |sum(imFechatresp),
       |sum(imFechatdownflow),
       |sum(imFechatdowntime),
       |sum(imFechatFlow),
       |sum(imFechatTime),
       |sum(imFetionsucc),
       |sum(imFetionreq),
       |sum(imFetionresptimeall),
       |sum(imFetionresp),
       |sum(imFetiondownflow),
       |sum(imFetiondowntime),
       |sum(imFetionFlow),
       |sum(imFetionTime),
       |sum(imQQsucc),
       |sum(imQQreq),
       |sum(imQQresptimeall),
       |sum(imQQresp),
       |sum(imQQdownflow),
       |sum(imQQdowntime),
       |sum(imQQFlow),
       |sum(imQQTime),
       |sum(imwechatsucc),
       |sum(imwechatreq),
       |sum(imwechatresptimeall),
       |sum(imwechatresp),
       |sum(imwechatdownflow),
       |sum(imwechatdowntime),
       |sum(imwechatFlow),
       |sum(imwechatTime),
       |sum(readcmreadsucc),
       |sum(readcmreadreq),
       |sum(readcmreadresptimeall),
       |sum(readcmreadresp),
       |sum(readcmreaddownflow),
       |sum(readcmreaddowntime),
       |sum(readcmreadFlow),
       |sum(readcmreadTime),
       |sum(readsurfsucc),
       |sum(readsurfreq),
       |sum(readsurfresptimeall),
       |sum(readsurfresp),
       |sum(readsurfdownflow),
       |sum(readsurfdowntime),
       |sum(readsurtFlow),
       |sum(readsurfTime),
       |sum(readQQReadsucc),
       |sum(readQQReadreq),
       |sum(readQQReadresptimeall),
       |sum(readQQReadresp),
       |sum(readQQReaddownflow),
       |sum(readQQReaddowntime),
       |sum(readQQReadFlow),
       |sum(readQQReadTime),
       |sum(weibo139wbsucc),
       |sum(weibo139wbreq),
       |sum(weibo139wbresptimeall),
       |sum(weibo139wbresp),
       |sum(weibo139wbdownflow),
       |sum(weibo139wbdowntime),
       |sum(weibo139wbFlow),
       |sum(weibo139wbTime),
       |sum(weibosinaWeibosucc),
       |sum(weibosinaWeiboreq),
       |sum(weibosinaWeiboresptimeall),
       |sum(weibosinaWeiboresp),
       |sum(weibosinaWeibodownflow),
       |sum(weibosinaWeibodowntime),
       |sum(weibosinaWeiboFlow),
       |sum(weibosinaWeiboTime),
       |sum(navigationheMapsucc),
       |sum(navigationheMapreq),
       |sum(navigationheMapresptimeall),
       |sum(navigationheMapresp),
       |sum(navigationheMapdownflow),
       |sum(navigationheMapdowntime),
       |sum(navigationheMapFlow),
       |sum(navigationheMapTime),
       |sum(navigationaMapsucc),
       |sum(navigationaMapreq),
       |sum(navigationaMapresptimeall),
       |sum(navigationaMapresp),
       |sum(navigationaMapdownflow),
       |sum(navigationaMapdowntime),
       |sum(navigationaMapFlow),
       |sum(navigationaMapTime),
       |sum(musicmigumusicsucc),
       |sum(musicmigumusicreq),
       |sum(musicmigumusicresptimeall),
       |sum(musicmigumusicresp),
       |sum(musicmigumusicdownflow),
       |sum(musicmigumusicdowntime),
       |sum(musicmigumusicFlow),
       |sum(musicmigumusicTime),
       |sum(musicqqmusicsucc),
       |sum(musicqqmusicreq),
       |sum(musicqqmusicresptimeall),
       |sum(musicqqmusicresp),
       |sum(musicqqmusicdownflow),
       |sum(musicqqmusicdowntime),
       |sum(musicqqmusicFlow),
       |sum(musicqqmusicTime),
       |sum(AppStoreMobileMarketsucc),
       |sum(AppStoreMobileMarketreq),
       |sum(AppStoreMobileMarketresptimeall),
       |sum(AppStoreMobileMarketresp),
       |sum(AppStoreMobileMarketdownflow),
       |sum(AppStoreMobileMarketdowntime),
       |sum(AppStoreMobileMarketFlow),
       |sum(AppStoreMobileMarketTime),
       |sum(AppStoreAppStoresucc),
       |sum(AppStoreAppStorereq),
       |sum(AppStoreAppStoreresptimeall),
       |sum(AppStoreAppStoreresp),
       |sum(AppStoreAppStoredownflow),
       |sum(AppStoreAppStoredowntime),
       |sum(AppStoreAppStoreFlow),
       |sum(AppStoreAppStoreTime),
       |sum(GamemiguGamesucc),
       |sum(GamemiguGamereq),
       |sum(GamemiguGameresptimeall),
       |sum(GamemiguGameresp),
       |sum(GamemiguGamedownflow),
       |sum(GamemiguGamedowntime),
       |sum(GamemiguGameFlow),
       |sum(GamemiguGameTime),
       |sum(Game4399Gamesucc),
       |sum(Game4399Gamereq),
       |sum(Game4399Gameresptimeall),
       |sum(Game4399Gameresp),
       |sum(Game4399Gamedownflow),
       |sum(Game4399Gamedowntime),
       |sum(Game4399GameFlow),
       |sum(Game4399GameTime),
       |sum(PayNFCSIMsucc),
       |sum(PayNFCSIMreq),
       |sum(PayNFCSIMresptimeall),
       |sum(PayNFCSIMresp),
       |sum(PayNFCSIMdownflow),
       |sum(PayNFCSIMdowntime),
       |sum(PayNFCSIMFlow),
       |sum(PayNFCSIMTime),
       |sum(PaymobilePaysucc),
       |sum(PaymobilePayreq),
       |sum(PaymobilePayresptimeall),
       |sum(PaymobilePayresp),
       |sum(PaymobilePaydownflow),
       |sum(PaymobilePaydowntime),
       |sum(PaymobilePayFlow),
       |sum(PaymobilePayTime),
       |sum(PayNFCsucc),
       |sum(PayNFCreq),
       |sum(PayNFCresptimeall),
       |sum(PayNFCresp),
       |sum(PayNFCdownflow),
       |sum(PayNFCdowntime),
       |sum(PayNFCFlow),
       |sum(PayNFCTime),
       |sum(Animemigudmsucc),
       |sum(Animemigudmreq),
       |sum(Animemigudmresptimeall),
       |sum(Animemigudmresp),
       |sum(Animemigudmdownflow),
       |sum(Animemigudmdowntime),
       |sum(AnimemigudmFlow),
       |sum(AnimemigudmTime),
       |sum(AnimeqqComicsucc),
       |sum(AnimeqqComicreq),
       |sum(AnimeqqComicresptimeall),
       |sum(AnimeqqComicresp),
       |sum(AnimeqqComicdownflow),
       |sum(AnimeqqComicdowntime),
       |sum(AnimeqqComicFlow),
       |sum(AnimeqqComicTime),
       |sum(mail139mailsucc),
       |sum(mail139mailreq),
       |sum(mail139mailresptimeall),
       |sum(mail139mailresp),
       |sum(mail139maildownflow),
       |sum(mail139maildowntime),
       |sum(mail139mailFlow),
       |sum(mail139mailTime),
       |sum(MailneteaseMailsucc),
       |sum(MailneteaseMailreq),
       |sum(MailneteaseMailresptimeall),
       |sum(MailneteaseMailresp),
       |sum(MailneteaseMaildownflow),
       |sum(MailneteaseMaildowntime),
       |sum(MailneteaseMailFlow),
       |sum(MailneteaseMailTime),
       |sum(financialsjsjsucc),
       |sum(financialsjsjreq),
       |sum(financialsjsjresptimeall),
       |sum(financialsjsjresp),
       |sum(financialsjsjdownflow),
       |sum(financialsjsjdowntime),
       |sum(financialsjsjFlow),
       |sum(financialsjsjTime),
       |sum(financialsjzqsucc),
       |sum(financialsjzqreq),
       |sum(financialsjzqresptimeall),
       |sum(financialsjzqresp),
       |sum(financialsjzqdownflow),
       |sum(financialsjzqdowntime),
       |sum(financialsjzqFlow),
       |sum(financialsjzqTime),
       |sum(financialzixuangusucc),
       |sum(financialzixuangureq),
       |sum(financialzixuanguresptimeall),
       |sum(financialzixuanguresp),
       |sum(financialzixuangudownflow),
       |sum(financialzixuangudowntime),
       |sum(financialzixuanguFlow),
       |sum(financialzixuanguTime),
       |sum(Othergdqqtsucc),
       |sum(Othergdqqtreq),
       |sum(Othergdqqtresptimeall),
       |sum(Othergdqqtresp),
       |sum(Othergdqqtdownflow),
       |sum(Othergdqqtdowntime),
       |sum(OthergdqqtFlow),
       |sum(OthergdqqtTime),
       |sum(Otherydzssucc),
       |sum(Otherydzsreq),
       |sum(Otherydzsresptimeall),
       |sum(Otherydzsresp),
       |sum(Otherydzsdownflow),
       |sum(Otherydzsdowntime),
       |sum(OtherydzsFlow),
       |sum(OtherydzsTime),
       |sum(Othernxbstsucc),
       |sum(Othernxbstreq),
       |sum(Othernxbstresptimeall),
       |sum(Othernxbstresp),
       |sum(Othernxbstdownflow),
       |sum(Othernxbstdowntime),
       |sum(OthernxbstFlow),
       |sum(OthernxbstTime),
       |sum(Othernxtsucc),
       |sum(Othernxtreq),
       |sum(Othernxtresptimeall),
       |sum(Othernxtresp),
       |sum(Othernxtdownflow),
       |sum(Othernxtdowntime),
       |sum(OthernxtFlow),
       |sum(OthernxtTime),
       |sum(Otheryjtsucc),
       |sum(Otheryjtreq),
       |sum(Otheryjtresptimeall),
       |sum(Otheryjtresp),
       |sum(Otheryjtdownflow),
       |sum(Otheryjtdowntime),
       |sum(OtheryjtFlow),
       |sum(OtheryjtTime),
       |sum(internetvisits),
       |sum(miguvisits),
       |sum(internetbusiness),
       |sum(migubusiness),
       |sum(ServiceIMresptimeall)
     """.stripMargin
  def analyse(implicit sparkSession: SparkSession): Unit = {
    tacDayAnalyse(sparkSession)
    cellDayAnalyse(sparkSession)
    spDayAnalyse(sparkSession)
    ueDayAnalyse(sparkSession)
    sgwDayAnalyse(sparkSession)
    imsicellDayAnalyse(sparkSession)
  }

  def tacDayAnalyse(implicit sparkSession: SparkSession): Unit = {
    import sparkSession.sql
    sql(s"use $DDB")
    sql(s"""alter table tac_day_http add if not exists partition(dt=$ANALY_DATE)
      """)
    sql(
      s"""
         |select
           |'$cal_date',
           |tac,
           |$kpibusinessday
           |from tac_hour_http where dt="$ANALY_DATE" group by tac
       """.stripMargin).write.mode(SaveMode.Overwrite).csv(s"$warhouseDir/tac_day_http/dt=$ANALY_DATE")

  }

  def cellDayAnalyse(implicit sparkSession: SparkSession): Unit = {
    import sparkSession.sql
    sql(s"use $DDB")
    sql(s"""alter table cell_day_http add if not exists partition(dt=$ANALY_DATE)
      """)
    sql(
      s"""
         |select
         |'$cal_date',
         |cellid,
         |$kpibusinessday
         |from cell_hour_http where dt="$ANALY_DATE" group by cellid
       """.stripMargin).write.mode(SaveMode.Overwrite).csv(s"$warhouseDir/cell_day_http/dt=$ANALY_DATE")

  }

  def spDayAnalyse(implicit sparkSession: SparkSession): Unit = {
    import sparkSession.sql
    sql(s"use $DDB")
    sql(s"""alter table sp_day_http add if not exists partition(dt=$ANALY_DATE)
      """)
    sql(
      s"""
         select
         |'$cal_date',
         |appserveripipv4,
         |$kpibusinessday
         |from sp_hour_http where dt="$ANALY_DATE" group by appserveripipv4
       """.stripMargin).write.mode(SaveMode.Overwrite).csv(s"$warhouseDir/sp_day_http/dt=$ANALY_DATE")
  }

  def ueDayAnalyse(implicit sparkSession: SparkSession): Unit = {
    import sparkSession.sql
    sql(s"use $DDB")
    sql(s"""alter table ue_day_http add if not exists partition(dt=$ANALY_DATE)
      """)
    sql(
      s"""
         |select
         |'$cal_date',
         |imsi,
         |msisdn,
         |$kpibusinessday
         |from ue_hour_http where dt="$ANALY_DATE" group by imsi,msisdn
       """.stripMargin).write.mode(SaveMode.Overwrite).csv(s"$warhouseDir/ue_day_http/dt=$ANALY_DATE")
  }

  def sgwDayAnalyse(implicit sparkSession: SparkSession): Unit = {
    import sparkSession.sql
    sql(s"use $DDB")
    sql(s"""alter table sgw_day_http add if not exists partition(dt=$ANALY_DATE)
      """)
    sql(
      s"""
         |select
         |'$cal_date',
         |sgwipaddr,
         |$kpibusinessday
         |from sgw_hour_http where dt="$ANALY_DATE" group by sgwipaddr
       """.stripMargin).write.mode(SaveMode.Overwrite).csv(s"$warhouseDir/sgw_day_http/dt=$ANALY_DATE")
  }

  def imsicellDayAnalyse(implicit sparkSession: SparkSession): Unit = {
    import sparkSession.sql
    sql(s"use $DDB")
    sql(s"""alter table imsi_cell_day_http add if not exists partition(dt=$ANALY_DATE)
      """)
    sql(
      s"""
         |select
         |'$cal_date',
         |imsi,
         |msisdn,
         |cellid,
         |$kpibusinessday
         |from imsi_cell_hour_http where dt="$ANALY_DATE" group by imsi,msisdn,cellid
       """.stripMargin).write.mode(SaveMode.Overwrite).csv(s"$warhouseDir/imsi_cell_day_http/dt=$ANALY_DATE")
  }

}
